package dao.impl.StoreDaoImpl;

import common.model.Entity.StoreEntity.Category;
import dao.StoreDao.CategoryDao;
import common.util.DBUtil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import java.util.Optional;

/**
 * 商品分类数据访问接口实现类，提供商品分类相关的数据库操作实现
 */
public class CategoryDaoImpl implements CategoryDao {
    private static final Logger logger = LoggerFactory.getLogger(CategoryDaoImpl.class);
    private final QueryRunner queryRunner = new QueryRunner();

    @Override
    public Optional<Category> findById(Long id) {
        String sql = "SELECT id, category_name AS name, parent_id AS parentId, " +
                "is_deleted AS isDeleted, created_at AS createdAt, updated_at AS updatedAt " +
                "FROM t_category WHERE id = ? AND is_deleted = 0";
        try (Connection conn = DBUtil.getConnection()) {
            Category category = queryRunner.query(conn, sql, new BeanHandler<>(Category.class), id);
            return Optional.ofNullable(category);
        } catch (SQLException e) {
            logger.error("根据ID查询分类失败: id={}", id, e);
            return Optional.empty();
        }
    }

    @Override
    public Optional<Category> findByName(String name) {
        String sql = "SELECT id,category_name AS name,parent_id AS parentId," +
                "is_deleted AS isDeleted,created_at AS createdAt,updated_at AS updatedAt " +
                "FROM t_category WHERE category_name = ? AND is_deleted = 0 LIMIT 1";
        try (Connection conn = DBUtil.getConnection()) {
            Category c = queryRunner.query(conn, sql, new BeanHandler<>(Category.class), name);
            return Optional.ofNullable(c);
        } catch (SQLException e) {
            logger.error("根据名称查询分类失败: {}", name, e);
            return Optional.empty();
        }
    }

    @Override
    public long insertAndReturnId(Category category) {
        String sql = "INSERT INTO t_category (category_name, parent_id, is_deleted, created_at) " +
                "VALUES (?, NULL, 0, CURRENT_TIMESTAMP)";
        try (Connection conn = DBUtil.getConnection()) {
            // 先按 Number 接，再取 longValue
            Number key = queryRunner.insert(conn, sql, new ScalarHandler<>(), category.getName());
            return key == null ? -1L : key.longValue();
        } catch (SQLException e) {
            logger.error("插入分类失败: name={}", category.getName(), e);
            throw new RuntimeException("插入分类失败", e);
        }
    }

    @Override
    public List<Category> findAll() {
        String sql = "SELECT id, category_name AS name, parent_id AS parentId, " +
                "is_deleted AS isDeleted, created_at AS createdAt, updated_at AS updatedAt " +
                "FROM t_category WHERE is_deleted = 0 ORDER BY created_at DESC";
        try (Connection conn = DBUtil.getConnection()) {
            return queryRunner.query(conn, sql, new BeanListHandler<>(Category.class));
        } catch (SQLException e) {
            logger.error("查询所有分类失败", e);
            return List.of();
        }
    }

    @Override
    public List<Category> findByParent(Long parentId) {
        String sql = "SELECT id, category_name AS name, parent_id AS parentId, " +
                "is_deleted AS isDeleted, created_at AS createdAt, updated_at AS updatedAt " +
                "FROM t_category WHERE parent_id = ? AND is_deleted = 0 ORDER BY created_at DESC";
        try (Connection conn = DBUtil.getConnection()) {
            return queryRunner.query(conn, sql, new BeanListHandler<>(Category.class), parentId);
        } catch (SQLException e) {
            logger.error("根据父分类ID查询分类失败: parentId={}", parentId, e);
            return List.of();
        }
    }

    @Override
    public boolean deleteById(Long id) {
        String sql = "UPDATE t_category SET is_deleted = 1, updated_at = CURRENT_TIMESTAMP WHERE id = ?";
        try (Connection conn = DBUtil.getConnection()) {
            int affected = queryRunner.update(conn, sql, id);
            return affected > 0;
        } catch (SQLException e) {
            logger.error("删除分类失败: id={}", id, e);
            return false;
        }
    }

    @Override
    public boolean save(Category category) {
        String sql = "INSERT INTO t_category (category_name, parent_id, is_deleted, created_at) " +
                "VALUES (?, ?, 0, CURRENT_TIMESTAMP)";
        try (Connection conn = DBUtil.getConnection()) {
            int affected = queryRunner.update(conn, sql,
                    category.getName(),
                    category.getParentId());
            return affected > 0;
        } catch (SQLException e) {
            logger.error("保存分类失败: {}", category.getName(), e);
            return false;
        }
    }

    @Override
    public boolean update(Category category) {
        String sql = "UPDATE t_category SET category_name = ?, parent_id = ?, " +
                " updated_at = CURRENT_TIMESTAMP WHERE id = ? AND is_deleted = 0";
        try (Connection conn = DBUtil.getConnection()) {
            int affected = queryRunner.update(conn, sql,
                    category.getName(),
                    category.getParentId(),
                    category.getId());
            return affected > 0;
        } catch (SQLException e) {
            logger.error("更新分类失败: id={}", category.getId(), e);
            return false;
        }
    }

    @Override
    public long count() {
        String sql = "SELECT COUNT(*) FROM t_category WHERE is_deleted = 0";
        try (Connection conn = DBUtil.getConnection()) {
            return queryRunner.query(conn, sql, new ScalarHandler<>());
        } catch (SQLException e) {
            logger.error("统计分类数量失败", e);
            return 0;
        }
    }
}